Adding a missing column to an Excel Template

Have you ever created an Excel Template, with all of the necessary edits, pivot tables, charts, etc. that you need to report, only to realise the first time you use it that it’s missing a vital column of data from CRM?  Typically, this means you have to redo all the work you did originally to include the missing column, wasting valuable time and energy.  One of my connections on LinkedIn posted this exact problem yesterday, and it got me thinking, surely there must be an easier way to solve this?

I think I’ve found a way!

Step 1 – Download your existing template

I’m sure if you’re reading this, then you know how to do this already, but if not, then navigate to the entity and view you’re going to be building the template from, then on the command ribbon click on Excel Templates, then find the specific template you want to use, and finally download it to Excel (note: don’t open it in Excel Online).

Step 2 – Realise you’ve missed the column you need

In my example, I’ve downloaded the template and opened it only to realise I’ve not included a column with the Created On date for the records.

Step 3 – Work out how the template is built

At this point, rather than downloading a new template and building my data analysis all over again, I thought I’d see if we could edit it in Excel.  I couldn’t see any hidden sheets, so I figured I’d see if there were any very hidden sheets.  I opened the VBA Editor (by pressing Alt+F11), and sure enough there was a sheet called hiddenDataSheet with a visibility property of 2 – xlSheetVeryHidden.  To open this sheet it was just a case of changing the visibility setting to -1 – xlSheetVisible

Step 4 – See what’s included in the Hidden Sheet

I opened the sheet I’ve now made visible, called hiddenSheet appropriately enough, and I could see a string of text in cell A1.  A quick look at this shows that it contains a string of references between CRM and the data table.  This was my eureka moment where I figured this might work!

Step 5 – Add missing column to the data table

As I mentioned above, I wanted to add the Created On date to my table, so I created a new column called Created On (clever, huh?), and formatted it as a Date field (pro tip, add data validation rules to the column to ensure it matches the standard templates)

Step 6 – Add a reference to the new column in the hiddenSheet

To update your hidden sheet to populate your new column with CRM data, you need to amend the string in hiddenSheet.  To the end of the string, add

&[your field reference]=[your column name]

replace [your field reference] with the logical name of the field you’re referring to in CRM (i.e. createdon for the Created On date)

replace [your column name] with the name of the new column you added to the data sheet.  Ensure you include capital letters where you have them in the new sheet, and replace all spaces with the URL encoding reference %20.

Thus, for my new Created On column, I added “&createdon=Created%20On”

Final Step – Add new Template to CRM

Package up your amended template, re-hide the hiddenSheet, then save it and upload to CRM.  Et voila, your new template should work!

 

Notes:

I’ve tested this with a few templates and it seems to work for me, no warranty is express or implied, use at your own risk, etc.

If you need to include a reference from a related entity, then you’ll need to include the GUID in the field reference and structure it as guid.logicalname

 

Hope this helps!